import pymysql
import hashlib
# 　密码转换
def encrytion(passwd):
    salt = b"Aid#2104"  # 盐字串
    hash = hashlib.sha256(salt)  # hash对象
    hash.update(passwd.encode())  # 要转换的密码
    return hash.hexdigest()
#数据库操作类
class Mysql:
    def __init__(self):
        self.kwargs = {
            "host": "localhost",
            "port": 3306,
            "user": "root",
            "password": "123456",
            "database": "dict",
            "charset": "utf8"
        }
        self.db = pymysql.connect(**self.kwargs) # 连接数据库


    def close(self):
        self.db.close()
    def cursor(self):
        self.cur = self.db.cursor()
    def enrol_infor(self,name,password):
        password=encrytion(password)
        sql = "insert into users (name,password) values (%s,%s);"
        try:
            self.cur.execute(sql, [name,password])
            self.db.commit()  # 提交事务
            return True
        except:
            self.db.rollback()
            return False


    def validate_enter(self, name, password):
        """
        验证用户信息是否有效
        :param name:
        :param password:
        :return:
        """
        password = encrytion(password)#binary:区分大小写
        sql = "select name from users where binary name=%s and password=%s ;"
        self.cur.execute(sql, [name, password])

        if self.cur.fetchone():
            return True
        else:
            return False

    def select_words(self,name,word):
        sql="select mean from words where word=%s;"
        self.cur.execute(sql, [word])
        one=self.cur.fetchone()# None (mean,)
        if one:
            self.insert_history(name,word)
            return one[0]

    def insert_history(self,name,word):
        # id  user_id words_id time
        sql="insert into history (users_id,words_id) values((select id from users where name=%s)," \
            "(select id from words where word=%s));"
        try:
            self.cur.execute(sql,[name,word])
            self.db.commit()
        except:
            self.db.rollback()
    def search_history(self,name):
        #name word time
        # sql="select name,word,time from users inner join history" \
        #     " on users.id=history.users_id " \
        #     "inner join words on words.id=history.words_id" \
        #     "where name=%s order by time desc limit 10;"
        sql = "select name,word,time from users " \
              "inner join history " \
              "on users.id=history.users_id " \
              "inner join words " \
              "on words.id=history.words_id " \
              "where name=%s " \
              "order by time desc " \
              "limit 10;"
        self.cur.execute(sql, [name])
        return self.cur.fetchall()  # ((xxxx),...) or ()





